from code.data.Subject import Subject
from code.dao.user_management import __check_user_exist
from code.dao.config import mysql_conn, cursor

import logging
import pymysql
import traceback
import time
import datetime


def add_subject(subject: Subject):
    if subject.userID is None:
        logging.error("UserID is required.")
        return False
    if not __check_user_exist(subject.userID):
        return False
    if subject.content is None:
        logging.error("Content is required.")
        return False
    sql = "INSERT INTO subject(content, userID) VALUES ('{}', '{}')".format(
        subject.content, subject.userID)
    try:
        cursor.execute(sql)
        mysql_conn.commit()
    except pymysql.Error as e:
        logging.error("Inserting subject into database failed: " + str(e.args[1]))
        mysql_conn.rollback()
        return False
    logging.info("Subject is added with SQL: " + sql)
    return True


def search_subject(subject: Subject, search_by: str = "all", fuzzy_search=False):
    subjects = []
    if search_by == "all":
        sql = "SELECT * FROM subject"
    elif search_by == "subjectID":
        if subject.subject_ID is None:
            logging.error("SubjectID is required.")
            return None
        sql = "SELECT * FROM subject WHERE subjectID = {}".format(subject.subject_ID)
    elif search_by == "time":
        if subject.time is None:
            logging.error("Time is required.")
            return None
        sql = "SELECT * FROM subject WHERE time = '{}'".format(subject.time)
        if fuzzy_search:
            sql = "SELECT * FROM subject WHERE time LIKE '%{}%'".format(subject.time)
    elif search_by == "content":
        if subject.content is None:
            logging.error("Content is required.")
            return None
        sql = "SELECT * FROM subject WHERE content = '{}'".format(subject.content)
        if fuzzy_search:
            sql = "SELECT * FROM subject WHERE content LIKE '%{}%'".format(subject.content)
    elif search_by == "userID":
        if subject.userID is None:
            logging.error("UserID is required.")
            return None
        sql = "SELECT * FROM subject WHERE userID = '{}'".format(subject.userID)
    else:
        logging.error("Attribute for search_by is not exist, use one in "
                      '["all", "subjectID", "time", "content", "userID"]')
        return None
    try:
        cursor.execute(sql)
        results = cursor.fetchall()
        if not results:
            logging.warning("Subject is not exist.")
            return None
    except pymysql.Error as e:
        logging.error("Searching for subject failed: " + str(e.args[1]))
        traceback.print_exc()
        return None
    for result in results:
        subjects.append(Subject(result[0], result[1], result[2], result[3]))
    logging.info("Search subject(s) with SQL: {}".format(sql))
    return subjects


def delete_subject(subject: Subject):
    if subject.subject_ID is None:
        logging.error("SubjectID is required.")
        return False
    if not __check_subject_exist(subject.subject_ID):
        return False
    sql = "DELETE FROM subject WHERE subjectID = {}".format(subject.subject_ID)
    try:
        cursor.execute(sql)
        mysql_conn.commit()
    except pymysql.Error as e:
        logging.error("Delete subject from database failed: " + str(e.args[1]))
        mysql_conn.rollback()
        return False
    logging.info("Subject is deleted with SQL: {}".format(sql))
    return True


def __check_subject_exist(subjectID: int):
    sql = "SELECT * FROM subject WHERE subjectID = {}".format(subjectID)
    try:
        cursor.execute(sql)
        results = cursor.fetchall()
        if not results:
            logging.error("Subject {} is not exist".format(subjectID))
            return False
    except pymysql.Error as e:
        logging.error("Searching for subject failed: " + str(e.args[1]))
        traceback.print_exc()
        return False
    return True


if __name__ == "__main__":
    subject1 = Subject(content="This is a test.", userID="admin")
    subject2 = Subject(content="This is another test.", userID="admin")
    print(add_subject(subject1))
    time.sleep(1)
    print(add_subject(subject2))
    results1 = search_subject(Subject())
    for result in results1:
        print(result.to_string())
    results2 = search_subject(Subject(content="ano"), "content", True)
    for result in results2:
        print(result.to_string())
    delete_subject(Subject(subjectID=1))
